from sqlalchemy.orm import Session
from fastapi import HTTPException
from day20.models.models_stu import Stu
from day20.models.models_emp import Emp
from day20.schema.schema_emp import EmpCreateScheme, EmpUpdateScheme, EmpResponseSchema



# 创建一条学生就业信息
def create_employment_service(db: Session, employment: EmpCreateScheme) ->EmpResponseSchema:
    # 验证学生是否存在
    db_student = db.query(Stu).filter(Stu.stu_id == employment.stu_id).first()
    if not db_student:
        raise HTTPException(status_code=404, detail=f"关联学生编号{employment.stu_id}不存在，无法记录就业信息")

    # 业务逻辑2：验证学生是否已存在就业记录（主键约束：1个学生1条就业记录，需求隐含）
    db_employment = db.query(Emp).filter(Emp.stu_id == employment.stu_id).first()
    if db_employment:
        raise HTTPException(status_code=400, detail=f"学生{employment.stu_id}已存在就业记录，如需修改请使用更新接口")

    # 业务逻辑3：处理冗余字段（未传则从学生表同步）
    employment_data = employment.dict()
    if not employment_data.get("stu_name"):
        employment_data["stu_name"] = db_student.stu_name
    if not employment_data.get("class_name"):
        employment_data["class_name"] = db_student.class_name

    # 插入就业记录
    db_employment = Emp(**employment_data)
    db.add(db_employment)
    db.commit()
    db.refresh(db_employment)
    return db_employment


# 通过学生id查询学生就业信息
def get_employment_by_studentid_service(
        db: Session,
        student_id: str = None
):
    # 验证学生是否存在
    db_student = db.query(Stu).filter(Stu.stu_id == student_id).first()
    if not db_student:
        raise HTTPException(status_code=404, detail=f"关联学生编号{student_id}不存在，无法查询就业信息")

    # 验证是否有就业信息
    db_employment = db.query(Emp).filter(Emp.stu_id == student_id).first()
    if not db_employment:
        raise HTTPException(status_code=404, detail=f"关联学生编号{student_id}未查询到就业记录")
    else:
        return db_employment


# 通过其他方式模糊查询查询学生就业信息
def get_employment_service(
        db: Session,
        company_name: str = None,
        min_salary: int = None,
        max_salary: int = None
):
    # 这一段有一个优先级的问题，splalchemy 的查询师链式的，每次查询的结果是上一次的叠加 而不是覆盖
    db_employment = db.query(Emp)
    if company_name:
        db_employment =db_employment.filter(Emp.emp_company_name.like(f"%{company_name}%"))
    if min_salary:
        db_employment =db_employment.filter(Emp.emp_salary >= min_salary)
    if max_salary:
        db_employment = db_employment.filter(Emp.emp_salary <= max_salary)
    return db_employment.all()


# 更改学生的就业信息
def update_employment_service(
        db: Session,
        student_id: str,
        employment_update: EmpUpdateScheme
) -> EmpUpdateScheme:
    db_employment = db.query(Emp).filter(Emp.stu_id == student_id).first()
    # 先判断学生是否存在
    if not db_employment:
        raise HTTPException(status_code=404, detail=f"学生{student_id}的就业记录不存在，无法修改")
    # 处理更新字段（支持部分更新）
    update_data = employment_update.dict(exclude_unset=True)
    for field, value in update_data.items():
        setattr(db_employment, field, value)

    db.commit()
    db.refresh(db_employment)
    return db_employment


# 删除学生就业信息
def delete_employment_service(
        db: Session,
        student_id: str
) -> dict:
    db_employment = db.query(Emp).filter(Emp.stu_id == student_id).first()
    if not db_employment:
        raise HTTPException(
            status_code=404,
            detail=f"学生「{student_id}」的就业记录不存在，无法删除"
        )

    db.delete(db_employment)
    db.commit()
    return {"detail": f"学生「{student_id}」的就业记录已成功删除"}